package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MissChen
 * @version 1.0
 * @date 2023 年12月12日 10:10
 */
public class Test05_分页查询 {
    public static void main(String[] args) {
        try {
            List<User> list=selectPage(2,5);
            for(User u:list){
                System.out.println(u);
            }
        } catch (DataAccessException e) {
            System.out.println(e.getMessage());
        }
    }

    public static List<User> selectPage(Integer pageNo,Integer pageSize) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<User> users=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" limit ?,?")
                    .toString();
            ps=conn.prepareStatement(sql);
            // 查询第pageNo页的数据，每页显示pageSize条   1    4
            // limit (pageNo-1)*pageSize,pageSize
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs=ps.executeQuery();

           while (rs.next()){
               User u=new User();
               u.setId(rs.getInt("id"));
               u.setUsername(rs.getString("username"));
               u.setPassword(rs.getString("password"));
               u.setPhone(rs.getString("phone"));
               u.setAddress(rs.getString("address"));
               users.add(u);
           }


        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException("访问数据库失败");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return users;
    }
}


